﻿using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SharePoint;
using SPQueryToSql.ApplicationObjects.CAML;

namespace SPQueryToSql.Extensions
{
    public static class SPQueryExtensions
    {
        public static string ToSQL(this SPQuery query, SPList list)
        {
            var q = new Query(query, list);
            return q.ToSQL(SelectMode.Fields);
        }

        public static int Count(this SPQuery query, SPList list)
        {
            var q = new Query(query, list);
            var sql = q.ToSQL(SelectMode.Count);
            var res = 0;
            var siteId = list.ParentWeb.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(
                () =>
                {
                    using (var site = new SPSite(siteId))
                    {
                        var conStr = site.ContentDatabase.DatabaseConnectionString;
                        using (var connection = new SqlConnection(conStr))
                        {
                            connection.Open();
                            var cmd = new SqlCommand(sql)
                                          {
                                              CommandType = CommandType.Text,
                                              Connection = connection
                                          };
                            var dbRes = cmd.ExecuteScalar();
                            res = dbRes == null ? 0 : (int)dbRes;
                            connection.Close();
                        }
                    }
                });
            return res;
        }

        public static bool Any(this SPQuery query, SPList list)
        {
            return query.Count(list) > 0;
        }

        public static int RandomId(this SPQuery query, SPList list)
        {
            var res = 0;
            var q = new Query(query, list);
            var sql = q.ToSQL(SelectMode.RandomId);
            var siteId = list.ParentWeb.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(
                () =>
                {
                    using (var site = new SPSite(siteId))
                    {
                        var conStr = site.ContentDatabase.DatabaseConnectionString;
                        using (var connection = new SqlConnection(conStr))
                        {
                            connection.Open();
                            var cmd = new SqlCommand(sql)
                            {
                                CommandType = CommandType.Text,
                                Connection = connection
                            };
                            var dbRes = cmd.ExecuteScalar();
                            res = dbRes == null ? 0 : (int)dbRes;
                            connection.Close();
                        }
                    }
                });
            return res;
        }
    }
}
